//========encoding:utf-8=========
/*
 * @describe:  
 * @author:cjgong
 * @copyright:chinasoft.cn
 * @date:2020年1月17日 上午10:35:39 
 */

package edu.chinasoft.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import edu.chinasoft.exception.UserNoExistsException;
import edu.chinasoft.pojo.Goods;
import edu.chinasoft.pojo.PageUtilForUser;
import edu.chinasoft.pojo.User;
import edu.chinasoft.util.JDBCTool;

public class UserDaoImp implements UserDao {
	private Logger logger = Logger.getLogger(UserDaoImp.class);

	@Override
	public User insertTable(User user) {
		logger.debug("UserDaoImp>>>insertTable  start ……");

		Connection connection = JDBCTool.getConnection();
		String sql = "INSERT INTO t_user(user_name,user_password,user_desc,user_flag)  VALUES(?,?,?,?);";
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			prepareStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			prepareStatement.setString(1, user.getUserName());
			prepareStatement.setString(2, user.getUserPassword());
			prepareStatement.setString(3, user.getUserDesc());
			prepareStatement.setString(4, user.getUserFlag());

			int num = prepareStatement.executeUpdate();

			if (num > 0) {
				resultSet = prepareStatement.getGeneratedKeys();

				int id = -1;
				if (resultSet.next()) {
					id = resultSet.getInt(1);
				}

				user.setId(id);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(resultSet, prepareStatement, connection);
		}
		logger.debug("UserDaoImp>>>insertTable  end ……");
		return user;
	}

	// 获取总记录数
	private void setRecordNum(PageUtilForUser pageUtilForUser) {
		logger.debug("UserDaoImp>>>setRecordNum  start ……");
		// 步骤1：从数据库获取总记录数
		Connection connection = JDBCTool.getConnection();
		String sql = "SELECT COUNT(*)  recordnum FROM t_user;";
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			prepareStatement = connection.prepareStatement(sql);
			resultSet = prepareStatement.executeQuery();
			if (resultSet.next()) {
				int recordNum = resultSet.getInt("recordnum");
				logger.debug("总记录数为：" + recordNum + "。");
				pageUtilForUser.setRecordNum(recordNum);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(resultSet, prepareStatement, connection);
		}

		// 步骤2：把获取的记录数保存到pageUtilForUser
		logger.debug("UserDaoImp>>>setRecordNum  end ……");

	}

	@Override
	public PageUtilForUser selectTablePage(int currentPage, int pageUtil) {
		PageUtilForUser pageUtilForUser = new PageUtilForUser();
		// 设置分页javabean 总记录数
		setRecordNum(pageUtilForUser);
		// 设置分页单位
		pageUtilForUser.setPageUtil(pageUtil);
		// 设置当前页
		pageUtilForUser.setCurrentPage(currentPage);

		// 查找分页里的记录
		Connection connection = JDBCTool.getConnection();
		String sql = "SELECT id,user_name,user_password,user_desc,user_flag FROM t_user    ORDER BY id DESC   LIMIT ?,?;";
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, (currentPage - 1) * pageUtil);
			prepareStatement.setInt(2, pageUtil);
			resultSet = prepareStatement.executeQuery();

			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String userName = resultSet.getString("user_name");
				String userPassword = resultSet.getString("user_password");
				String user_desc = resultSet.getString("user_desc");
				String user_flag = resultSet.getString("user_flag");

				User user = new User(id, userName, userPassword, user_desc, user_flag);
				pageUtilForUser.getUserList().add(user);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(resultSet, prepareStatement, connection);
		}

		return pageUtilForUser;
	}

	@Override
	public User selectUserById(User user) {
		logger.debug("UserDaoImp>>>selectUserById  start ……");
		// 查找分页里的记录
		User result = null;
		Connection connection = JDBCTool.getConnection();
		String sql = "SELECT id,user_name,user_password,user_desc,user_flag FROM t_user  WHERE id=?;";
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		try {
			prepareStatement = connection.prepareStatement(sql);

			prepareStatement.setInt(1, user.getId());
			resultSet = prepareStatement.executeQuery();

			while (resultSet.next()) {

				String user_name = resultSet.getString("user_name");
				String user_password = resultSet.getString("user_password");
				String user_desc = resultSet.getString("user_desc");
				String user_flag = resultSet.getString("user_flag");

				result = new User(user.getId(), user_name, user_password, user_desc, user_flag);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(resultSet, prepareStatement, connection);
		}
		logger.debug("UserDaoImp>>>selectUserById  start ……");
		return result;
	}

	@Override
	public boolean updateUserById(User user) {

		logger.debug("UserDaoImp>>>updateUserById  start ……");
		boolean result = false;
		Connection connection = JDBCTool.getConnection();
		String sql = "UPDATE t_user  SET user_name=?,user_password=?,user_desc=?,user_flag=? WHERE id=?;";
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setString(1, user.getUserName());
			prepareStatement.setString(2, user.getUserPassword());
			prepareStatement.setString(3, user.getUserDesc());
			prepareStatement.setString(4, user.getUserFlag());
			prepareStatement.setInt(5, user.getId());

			int num = prepareStatement.executeUpdate();

			if (num > 0) {
				result = true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(null, prepareStatement, connection);
		}
		logger.debug("UserDaoImp>>>updateUserById  end ……");

		return result;
	}
	@Override
	public boolean  deleteUserById(User user) {

		logger.debug("UserDaoImp>>>deleteUserById  start ……");
		boolean result = false;
		Connection connection = JDBCTool.getConnection();
		String sql = "DELETE FROM t_user  WHERE id=?;";
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = connection.prepareStatement(sql);

			prepareStatement.setInt(1, user.getId());

			int num = prepareStatement.executeUpdate();

			if (num > 0) {
				result = true;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(null, prepareStatement, connection);
		}
		logger.debug("UserDaoImp>>>deleteUserById  end ……");

		return result;
	}
	
	@Override
	public  User  selectUserByNameAndPassword(User user) throws UserNoExistsException {
		logger.debug("UserDaoImp>>>selectUser  start ……");
		Connection connection = JDBCTool.getConnection();
		String sql="SELECT id,user_name,user_password FROM t_user WHERE  user_name=? AND user_password=?;";
		PreparedStatement prepareStatement=null;
		ResultSet resultSet=null;
		try {
			prepareStatement = connection.prepareStatement(sql);
			
			prepareStatement.setString(1, user.getUserName());
			prepareStatement.setString(2, user.getUserPassword());
			
			resultSet = prepareStatement.executeQuery();
			
			if(resultSet.next()){
				int id = resultSet.getInt("id");
				user.setId(id);
			}else{
				throw new UserNoExistsException("用户不存在！");
			}
		} catch (SQLException  e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UserNoExistsException  e) {
			// TODO Auto-generated catch block
			throw e;
		} catch (Exception  e) {
			// TODO Auto-generated catch block
			throw e;
		} finally{
			JDBCTool.closeJdbc(resultSet, prepareStatement, connection);
		}
		logger.debug("UserDaoImp>>>selectUser  end ……");
		return user;
	}
	
}
